package com.ifonly.activerecord;

import com.ifonly.activerecord.common.Pagination;
import com.ifonly.activerecord.dialect.Dialect;
import com.ifonly.activerecord.exceptions.ActiveRecordException;
import com.ifonly.activerecord.exceptions.SqlExecuteException;
import com.ifonly.activerecord.exceptions.TableMapException;
import com.ifonly.activerecord.helpers.SqlInfo;
import com.ifonly.activerecord.sql.ResultSetHandler;
import com.ifonly.activerecord.sql.handlers.ListModelResultSetHandler;
import com.ifonly.activerecord.sql.handlers.ListRecordResultSetHandler;
import com.ifonly.activerecord.sql.handlers.ScalarResultSetHandler;
import com.ifonly.activerecord.tx.ITx;
import com.ifonly.common.StringPool;
import com.ifonly.config.Configuration;
import com.ifonly.utils.ArrayUtils;

import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * @author <a href="mailto:ifonlymaster@163.com">ifonly</a>
 * @version 1.0 2015-12-18 13:16
 * @since JDK 1.6
 */
public class DbUtils {
    protected static Db db = Db.getInstance(); // instance of db
    protected static Configuration cfg = Configuration.getInstance();

    /**
     * get table mapped with class
     *
     * @param clazz model class
     * @return mapped Table
     */
    public static Table getTableInMapper(Class<? extends Model> clazz) {
        Table table = db.getTable(clazz);
        if (table == null) {
            throw new TableMapException("The class [" + clazz.getName() + "] does not mapping");
        }

        return table;
    }

    // ------------------------------------------------------------- close

    public static void close(Connection conn) throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    public static void close(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }

    public static void close(Statement stmt) throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
    }

    public static void close(ResultSet rs, Statement stmt) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }


    // ------------------------------------------------------------------------ PreparedStatement


    /**
     * create PreparedStatement
     *
     * @param conn   Connection
     * @param sql    Sql String
     * @param params PreparedStatement Parameter
     * @return PreparedStatement
     * @throws SQLException throw SQLException
     */
    protected static PreparedStatement preparedStatement(Connection conn, String sql, Object... params) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(sql);
        if (params != null) {
            fillPreparedStatement(ps, params);
        }
        return ps;
    }

    /**
     * create PreparedStatement where return generatedKeys
     *
     * @param conn   Connection
     * @param pks    generatedKeys maybe null or empty
     * @param sql    Sql String
     * @param params PreparedStatement Parameter
     * @return PreparedStatement
     * @throws SQLException throw SQLException
     */
    protected static PreparedStatement preparedStatement(Connection conn, String[] pks, String sql, Object... params) throws SQLException {
        PreparedStatement ps;
        if (ArrayUtils.isNullOrEmpty(pks)) {
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        } else {
            ps = conn.prepareStatement(sql, pks);
        }

        fillPreparedStatement(ps, params);
        return ps;
    }

    /**
     * fill PreparedStatement Parameter
     *
     * @param ps     PreparedStatement
     * @param params Parameter
     * @throws SQLException throw SQLException
     */
    protected static void fillPreparedStatement(PreparedStatement ps, Object... params) throws SQLException {
        if (params != null && params.length > 0) {
            try {
                for (int i = 0, len = params.length; i < len; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                if (cfg.isDevMode()) {
                    cfg.getReporter().report("Parameter: " + ArrayUtils.toString(params));
                    cfg.getReporter().report("");
                }
            } catch (SQLException e) {
                throw new SqlExecuteException(e);
            }
        }
    }

    /**
     * fill PreparedStatement with a JavaBean
     *
     * @param ps            PreparedStatement
     * @param bean          JavaBean
     * @param propertyNames the properties to fill
     * @throws SQLException throw SQLException
     */
    protected static void fillPreparedStatementWithBean(PreparedStatement ps, Object bean,
                                                        String... propertyNames) throws SQLException {
        PropertyDescriptor[] descriptors;
        try {
            descriptors = Introspector.getBeanInfo(bean.getClass())
                    .getPropertyDescriptors();
        } catch (IntrospectionException e) {
            throw new RuntimeException("Couldn't introspect bean "
                    + bean.getClass().toString(), e);
        }
        PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
        for (int i = 0; i < propertyNames.length; i++) {
            String propertyName = propertyNames[i];
            if (propertyName == null) {
                throw new NullPointerException("propertyName can't be null: " + i);
            }

            boolean found = false;
            for (PropertyDescriptor descriptor : descriptors) {
                if (propertyName.equals(descriptor.getName())) {
                    sorted[i] = descriptor;
                    found = true;
                    break;
                }
            }
            if (!found) {
                throw new RuntimeException("Couldn't find bean property: "
                        + bean.getClass() + " " + propertyName);
            }
        }
        fillPreparedStatementWithBean(ps, bean, sorted);
    }

    /**
     * fill PreparedStatement with a JavaBean
     *
     * @param ps         PreparedStatement to fill
     * @param bean       JavaBean
     * @param properties the properties to fill
     * @throws SQLException throw SQLException
     */
    protected static void fillPreparedStatementWithBean(PreparedStatement ps, Object bean,
                                                        PropertyDescriptor[] properties) throws SQLException {
        Object[] params = new Object[properties.length];
        for (int i = 0; i < properties.length; i++) {
            PropertyDescriptor property = properties[i];
            Object value;
            Method method = property.getReadMethod();
            if (method == null) {
                throw new RuntimeException("No read method for bean property "
                        + bean.getClass() + " " + property.getName());
            }
            try {
                value = method.invoke(bean);
            } catch (InvocationTargetException e) {
                throw new RuntimeException("Couldn't invoke method: " + method,
                        e);
            } catch (IllegalArgumentException e) {
                throw new RuntimeException(
                        "Couldn't invoke method with 0 arguments: " + method, e);
            } catch (IllegalAccessException e) {
                throw new RuntimeException("Couldn't invoke method: " + method,
                        e);
            }
            params[i] = value;
        }
        fillPreparedStatement(ps, params);
    }

    /**
     * fill PreparedStatement with a Model
     *
     * @param ps         PreparedStatement
     * @param model      Model
     * @param properties properties to fill
     * @throws SQLException throw SQLException
     */
    protected static void fillPreparedStatementWithModel(PreparedStatement ps, Model model, String... properties) throws SQLException {
        Object[] params = new Object[properties.length];
        Map<String, Object> propertyAndValues = model.getPropertyAndValues();

        for (int i = 0, len = params.length; i < len; i++) {
            params[i] = propertyAndValues.get(properties[i]);
        }

        fillPreparedStatement(ps, params);
    }

    /* ------------ sql method ---------------- */

    private static int execute(Connection conn, String sql, Object[] params) {
        PreparedStatement ps = null;
        try {
            ps = preparedStatement(conn, sql, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                close(ps);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    // ------------------------------------------------------------ tx

    /**
     * execute transaction
     *
     * @param tx Tx interface
     * @return true - execute success | false - execute failed
     */
    public static boolean tx(ITx tx) {
        Connection conn = db.getThreadLocalConnection();
        if (conn != null) {
            boolean result = tx.exec();
            if (result) {
                return true;
            }
            throw new ActiveRecordException("Tx exec return false, the outer transaction need return false");
        }

        Boolean autoCommit = null;
        try {
            conn = db.getConnection();
            autoCommit = conn.getAutoCommit();
            db.setThreadLocalConnection(conn);
            if (conn.getTransactionIsolation() < db.getTransactionIsolation()) {
                conn.setTransactionIsolation(db.getTransactionIsolation());
            }
            conn.setAutoCommit(false);
            boolean result = tx.exec();
            if (result)
                conn.commit();
            else
                conn.rollback();
            return result;
        } catch (Throwable throwable) {
                if (conn != null) {
                    try {
                        conn.rollback();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                throw new RuntimeException(throwable);
        } finally {
            if (conn != null) {
                try {
                    if (autoCommit != null) {
                        conn.setAutoCommit(autoCommit);
                    }
                } catch (SQLException e) {
                    // ignore
                }
            }
            db.closeAndRemoveThreadLocalConnection();
        }
    }

    // ------------------------------------------------------------ insert

    /**
     * execute the given insert sql statement.
     *
     * @param conn   Connection
     * @param sql    sql to execute
     * @param params replacement parameters
     * @return a int value is the result of executeUpdate
     */
    public static int insert(Connection conn, String sql, Object... params) {
        return execute(conn, sql, params);
    }

    /**
     * execute the given insert sql statement, while return the GeneratedKeys
     *
     * @param conn   Connection
     * @param pks    GeneratedKeys column names
     * @param sql    sql to execute
     * @param params replacement parameters
     * @return GeneratedKeys ResultSet
     */
    public static ResultSet insert(Connection conn, String[] pks, String sql, Object... params) {
        try {
            PreparedStatement ps = preparedStatement(conn, pks, sql, params);
            ps.executeUpdate();
            return ps.getGeneratedKeys();
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        }
    }

    /**
     * execute the given insert sql statement,
     * return the handled GeneratedKeys by ResultSetHandler
     *
     * @param conn    Connection
     * @param handler ResultSetHandler
     * @param pks     GeneratedKeys column names
     * @param sql     sql to execute
     * @param params  replacement parameters
     * @param <T>     ResultSetHandler Type
     * @return handled GeneratedKeys
     */
    public static <T> T insert(Connection conn, ResultSetHandler<T> handler,
                               String[] pks, String sql, Object... params) {
        T generatedKeys = null;
        ResultSet rs = null;
        try {
            rs = insert(conn, pks, sql, params);
            generatedKeys = handler.handle(rs);
        } catch (Exception e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                close(rs);
            } catch (SQLException e) {
                // ignore
            }
        }
        return generatedKeys;
    }

    // ------------------------------------------------------------ update

    /**
     * execute update sql
     *
     * @param conn   Connection
     * @param sql    update sql
     * @param params replacement parameters
     * @return a int value is the result of executeUpdate
     */
    public static int update(Connection conn, String sql, Object... params) {
        return execute(conn, sql, params);
    }

    /**
     * execute update sql
     *
     * @param sql    update sql
     * @param params replacement parameters
     * @return a int value is the result of executeUpdate
     */
    public static int update(String sql, Object... params) {
        Connection conn = null;
        try {
            conn = db.getConnection();
            return update(conn, sql, params);
        } catch (Exception e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                db.closeConnection(conn);
            } catch (Exception e) {
                // ignore
            }
        }
    }

    // ------------------------------------------------------------ delete

    /**
     * execute delete sql
     *
     * @param conn   Connection
     * @param sql    delete sql
     * @param params replacement parameters
     * @return a int value is the result of executeUpdate
     */
    public static int delete(Connection conn, String sql, Object[] params) {
        return execute(conn, sql, params);
    }

    public static int delete(String sql, Object...params) {
        Connection conn = null;
        try {
            conn = db.getConnection();
            return delete(conn, sql, params);
        } catch (Exception e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                db.closeConnection(conn);
            } catch (Exception e) {
                // ignore
            }
        }
    }

    // ------------------------------------------------------------ query

    /**
     * execute query sql
     *
     * @param conn   Connection
     * @param sql    query sql
     * @param params replacement parameters
     * @return a int value is the result of executeUpdate
     */
    public static ResultSet query(Connection conn, String sql, Object... params) {
        try {
            PreparedStatement ps = preparedStatement(conn, sql, params);
            return ps.executeQuery();
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        }
    }

    /**
     * execute query sql , while return a handled value by ResultSetHandler
     *
     * @param conn Connection
     * @param handler ResultSetHandler
     * @param sql query sql
     * @param params replacement parameters
     * @param <T> type of return
     * @return a handled value
     */
    public static <T> T query(Connection conn, ResultSetHandler<T> handler, String sql, Object... params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = preparedStatement(conn, sql, params);
            rs = ps.executeQuery();
            return handler.handle(rs);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                close(rs, ps);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    // ------------------------------------------------------------ batch

    /**
     * execute batch sql
     *
     * @param conn Connection
     * @param sql batch sql
     * @param params replacement parameters
     * @return The number of rows updated in the batch.
     */
    public static int[] batch(Connection conn, String sql, Object[][] params) {
        PreparedStatement stmt = null;
        int[] rows = null;
        try {
            stmt = preparedStatement(conn, sql);

            for (Object[] param : params) {
                fillPreparedStatement(stmt, param);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                close(stmt);
            } catch (SQLException e) {
                // ignore
            }
        }
        return rows;
    }


    // ------------------------------------------------------------ insert batch

    /**
     * insert batch and return GeneratedKeys
     *
     * @param conn Connection
     * @param pks GeneratedKey columns
     * @param sql insert sql
     * @param params replacement parameters
     * @return GeneratedKeys
     */
    public static ResultSet insertBatch(Connection conn, String[] pks, String sql, Object[][] params) {
        try {
            PreparedStatement ps = preparedStatement(conn, pks, sql);

            for (Object[] param : params) {
                fillPreparedStatement(ps, param);
                ps.addBatch();
            }
            ps.executeBatch();
            return ps.getGeneratedKeys();
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        }
    }

    /**
     * insert batch and return GeneratedKeys
     *
     * @param conn Connection
     * @param sql insert sql
     * @param params replacement parameters
     * @return GeneratedKeys
     */
    public static ResultSet insertBatch(Connection conn, String sql, Object[][] params) {
        try {
            PreparedStatement ps = preparedStatement(conn, StringPool.EMPTY_ARRAY, sql);

            for (Object[] param : params) {
                fillPreparedStatement(ps, param);
                ps.addBatch();
            }
            ps.executeBatch();
            return ps.getGeneratedKeys();
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        }
    }

    /**
     * insert batch and return handled GeneratedKeys
     *
     * @param conn Connection
     * @param handler ResultSetHandler
     * @param pks GeneratedKey columns
     * @param sql insert sql
     * @param params replacement parameters
     * @param <T> return type
     * @return handled GeneratedKeys
     */
    public static <T> T insertBatch(Connection conn, ResultSetHandler<T> handler,  String[] pks, String sql, Object[][] params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = preparedStatement(conn, pks, sql);

            for (Object[] param : params) {
                fillPreparedStatement(ps, param);
                ps.addBatch();
            }
            ps.executeBatch();
            rs = ps.getGeneratedKeys();
            return handler.handle(rs);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                close(rs, ps);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    /**
     * insert batch and return handled GeneratedKeys
     *
     * @param conn Connection
     * @param handler ResultSetHandler
     * @param sql insert sql
     * @param params replacement parameters
     * @param <T> return type
     * @return handled GeneratedKeys
     */
    public static <T> T insertBatch(Connection conn, ResultSetHandler<T> handler, String sql, Object[][] params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = preparedStatement(conn, StringPool.EMPTY_ARRAY, sql);

            for (Object[] param : params) {
                fillPreparedStatement(ps, param);
                ps.addBatch();
            }
            ps.executeBatch();
            rs = ps.getGeneratedKeys();
            return handler.handle(rs);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                close(rs, ps);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    public static ResultSet insertBatch(String sql, Object[][]params) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            conn = db.getConnection();
            return insertBatch(conn, sql, params);
        } catch (Exception e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                DbUtils.close(ps);
                db.closeConnection(conn);
            } catch (SQLException e) {
                // ignore
            }
        }
    }


    // -------------------------------------------------------------- ar sql helper

    public static Record query(String sql, Object... params) {
        List<Record> records = list(sql, params);
        if (records == null || records.isEmpty()) {
            return null;
        }
        if (records.size() > 1) {
            throw new SqlExecuteException("not a unique result, size[" + records.size() + "]");
        }
        return records.get(0);
    }

    public static List<Record> list(String sql, Object... params) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            conn = db.getConnection();
            ps = DbUtils.preparedStatement(conn, sql, params);
            rs = ps.executeQuery();

            ListRecordResultSetHandler handler = new ListRecordResultSetHandler();
            return handler.handle(rs);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                DbUtils.close(ps);
                db.closeConnection(conn);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    public static Pagination<Record> pagination(String sql, int offset, int limit, Object... params) {
        Dialect dialect = db.getDialect();
        SqlInfo dataSql = dialect.sql(sql, offset, limit, params);
        String countSql = dialect.count(sql);

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = db.getConnection();
            ps = DbUtils.preparedStatement(conn, dataSql.getSql(), dataSql.getParams());
            rs = ps.executeQuery();
            ResultSetHandler<List<Record>> dataHandler = new ListRecordResultSetHandler();
            List<Record> records = dataHandler.handle(rs);

            ps = DbUtils.preparedStatement(conn, countSql, params);
            rs = ps.executeQuery();

            ScalarResultSetHandler<Number> countHandler = new ScalarResultSetHandler<Number>(Dialect.COUNT_KEY);
            long count = countHandler.handle(rs).longValue();

            int pageNumber = offset / limit + 1;

            return new Pagination<Record>(pageNumber, limit, count, records);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                DbUtils.close(ps);
                db.closeConnection(conn);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static <T extends Model> T  query(Class<? extends Model> clazz, String sql, Object... params) {
        List<? extends Model> models = list(clazz, sql, params);
        if (models == null || models.isEmpty()) {
            return null;
        }
        if (models.size() > 1) {
            throw new SqlExecuteException("Not a unique, size of result[" + models.size() + "]");
        }
        return (T) models.get(0);
    }

    @SuppressWarnings("unchecked")
    public static <T> List<T> list(Class<T> clazz, String sql, Object... params) {
        if (!Model.class.isAssignableFrom(clazz)) {
            throw new IllegalArgumentException("class must be implemented Model");
        }

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = db.getConnection();
            ps = DbUtils.preparedStatement(conn, sql, params);
            rs = ps.executeQuery();
            ListModelResultSetHandler handler = new ListModelResultSetHandler((Class<? extends Model>) clazz);
            return (List<T>) handler.handle(rs);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                DbUtils.close(ps);
                db.closeConnection(conn);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    public static Pagination<? extends Model> pagination(Class<? extends Model> clazz, String sql, int offset, int limit, Object... params) {
        Dialect dialect = db.getDialect();
        SqlInfo dataSql = dialect.sql(sql, offset, limit, params);
        String countSql = dialect.count(sql);

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = db.getConnection();
            ps = DbUtils.preparedStatement(conn, dataSql.getSql(), dataSql.getParams());
            rs = ps.executeQuery();
            ListModelResultSetHandler dataHandler = new ListModelResultSetHandler(clazz);
            List<Model> records = dataHandler.handle(rs);

            ps = DbUtils.preparedStatement(conn, countSql, params);
            rs = ps.executeQuery();

            ScalarResultSetHandler<Number> countHandler = new ScalarResultSetHandler<Number>(Dialect.COUNT_KEY);
            long count = countHandler.handle(rs).longValue();

            int pageNumber = offset / limit + 1;

            return new Pagination<Model>(pageNumber, limit, count, records);
        } catch (SQLException e) {
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                DbUtils.close(ps);
                db.closeConnection(conn);
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    //------------------------------------------------ find

    @SuppressWarnings("unchecked")
    public static <T> T findByPrimaryKey(Class<T> clazz, Object pk) {
        Class<? extends Model> modelClass = (Class<? extends Model>) clazz;
        Table table = db.getTable(modelClass);
        if (table == null) {
            throw new IllegalArgumentException("No table mapping for class [" + clazz.getName() + "]");
        }
        String tableName = table.getTableName();
        if (tableName == null) {
            throw new IllegalArgumentException("No table name mapping for class [" + clazz.getName() + "]");
        }
        String[] pks = table.getPrimaryKeys();
        if (pks == null || pks.length == 0) {
            throw new IllegalArgumentException("The table("+ tableName +") no primary keys");
        }
        String sql = "SELECT * FROM " + tableName + " WHERE " + pks[0] + " = ?";
        return (T) query(modelClass, sql, pk);
    }


    //-------------------------------------------------- help

    public static boolean isNew(Model model) {
        if (model.get("id") == null) {
            return true;
        }
        Number number = model.getNumber("id");
        return number == null || number.longValue() == 0;
    }
}
